#!/usr/bin/env python
# -*- coding: utf-8 -*-
# SQL type wrappers audited by Joshua Kugler, 2008-02-20
"""Module vehicles selects/add/updates/deletes vehicles from the database.
Module vehicles also selects available_vehicles based on a start date and end date."""

from string import Template
from tack.db import query, sql_date, sql_int, sql_string
from tack.lib import string_app, content_headers, status_codes, json_app
from tack.lib.jsontools import json_dumps
from tack.lib.dicttools import query_to_list
from commitment_conflict import set_to_conflicted_using_vehicle
import datetime
from datetime import datetime
import time

select_all_vehicles = """
SELECT vehicle_id, vehicleno, capacity, type, tagno, doortype, weight, status
FROM tmpo_vehicle
WHERE active=1
"""

select_vehicle_data = Template("""
SELECT vehicle_id, vehicleno, capacity, type, tagno, doortype, weight, status
FROM tmpo_vehicle 
WHERE vehicle_id = $vehicle_id
""")

add_vehicle_data = Template("""
INSERT INTO tmpo_vehicle (capacity, vehicleno, type, tagno, doortype, weight, status)
VALUES ( $capacity, $vehicleno, $type, $tagno, $doortype, $weight, $status )
""")

update_vehicle_data = Template("""
UPDATE tmpo_vehicle
SET capacity=$capacity, vehicleno=$vehicleno, type=$type, 
    tagno=$tagno, doortype=$doortype, weight=$weight, status=$status
WHERE tmpo_vehicle.vehicle_id = $vehicle_id
""")

logical_delete_vehicle = Template("""
UPDATE tmpo_vehicle
SET active=0
WHERE vehicle_id = $vehicle_id
""")

select_available_vehicles = """
SELECT
    vehicle_id, vehicleno, capacity, type, tagno, doortype, weight, status
FROM tmpo_vehicle
WHERE active = 1 AND ( vehicle_id NOT IN ( 
    SELECT vehicle_id FROM tmpo_vehicle
    WHERE (status = 1)
    )
AND vehicle_id NOT IN (
    SELECT vehicle_id FROM tmpo_schedule_resourced 
    WHERE (vehicle_id IS NOT NULL
        AND start <= '$start' AND finish >= '$finish'
        AND id != $record_id AND (parent_id != $record_id OR parent_id IS NULL))
    OR (vehicle_id IS NOT NULL AND start >= '$start' AND start <= '$finish'
        AND id != $record_id AND (parent_id != $record_id OR parent_id IS NULL)) 
    OR (vehicle_id IS NOT NULL AND finish >= '$start' AND finish <= '$finish'
        AND id != $record_id AND (parent_id != $record_id OR parent_id IS NULL)) 
))
"""

get_vehicle_list = Template("""
SELECT vehicle_id, status
FROM tmpo_vehicle
WHERE vehicle_id = $vehicle_id
""")

get_affected_events_sql = Template("""
SELECT id, parent_id FROM tmpo_schedule_resourced
WHERE (vehicle_id = $vehicle_id AND start <= '$start' AND start >= '$finish') 
OR (vehicle_id = $vehicle_id AND start >= '$start' AND start <= '$finish') 
OR (vehicle_id = $vehicle_id AND finish >= '$start' AND finish <= '$finish')
""")

def get_all_vehicles(environ, start_response):
    """Function get_all_vehicles SELECTS all vehicles in the database."""
    sql_statement = select_all_vehicles
    rows = query(environ['connection'], sql_statement)
    output = {}
    labels = ['id', 'vehicleno', 'capacity', 'type', 'tagno', 'doortype', 'weight', 'status'] 
    output['vehicles'] = [dict(zip(labels,i)) for i in rows]
    return json_app(output)(environ, start_response)

def get_vehicle_data(environ, start_response):
    """Function get_vehicle_data SELECTS one vehicles data with the vehicles id as 
    the parameter."""
    sql_statement = select_vehicle_data.substitute(vehicle_id=environ['dispatcher.groups']['vehicle_id'])
    rows = query(environ['connection'], sql_statement)
    output = {}
    labels = ['id', 'vehicleno', 'capacity', 'type', 'tagno', 'doortype', 'weight', 'status']  
    output['vehicles'] = [dict(zip(labels,i)) for i in rows]
    return json_app(output)(environ, start_response)

def add_vehicle(environ, start_response):
    """Function add_vehicle INSERTS new vehicle data into the database with form variables."""
    form = environ['form']
    sql_statement = add_vehicle_data.substitute(
     capacity = sql_int(form['capacity']),
     vehicleno = sql_string(form['vehicleno']),
     type = sql_int(form['type']),
     tagno = sql_string(form['tagno']),
     doortype = sql_string(form['doortype']),
     weight = sql_int(form['weight']),
     status = sql_int(form['status'])
    )
    query(environ['connection'], sql_statement)
    rows = query(environ['connection'], 'SELECT CAST(@@IDENTITY AS INT)')
    return json_app(rows)(environ, start_response)

def update_vehicle(environ, start_response):
    """Function update_vehicle UPDATES vehicle data in the database with the vehicle id as 
    the parameter."""
    form = environ['form']
    sql_statement = update_vehicle_data.substitute(
     vehicle_id = environ['dispatcher.groups']['vehicle_id'],
     capacity = sql_int(form['capacity']),
     vehicleno = sql_string(form['vehicleno']),
     type = sql_int(form['type']),
     tagno = sql_string(form['tagno']),
     doortype = sql_string(form['doortype']),
     weight = sql_int(form['weight']),
     status = sql_int(form['status'])
    )
    rows = query(environ['connection'], sql_statement)
    sql = get_vehicle_list.substitute( 
          vehicle_id = environ['dispatcher.groups']['vehicle_id'],
          status = sql_int(form['status'])
    )
    event_info = query(environ['connection'], sql)
    vehicle_id = environ['dispatcher.groups']['vehicle_id']
    throwaway = datetime.now()
    start = "'" + throwaway.strftime('%Y-%m-%d %H:%M') + "'"
    finish = "'3000-01-01 12:00'"
    set_to_conflicted_using_vehicle(environ, vehicle_id, start, finish)
    output = 'Vehicle Updated'
    return json_app(output)(environ, start_response)

def delete_vehicle(environ, start_response):
    """Function delete_vehicle Logicly DELETES a vechicle from the database with the vehicle
    id as the parameter.  The vehicle is not completely deleted, it is set to active = false."""
    sql_statement = logical_delete_vehicle.substitute(vehicle_id=environ['dispatcher.groups']['vehicle_id'])
    rows = query(environ['connection'], sql_statement)
    output = 'Vehicle Deactivated'
    return json_app(output)(environ, start_response)

def get_available_vehicles(environ, start_response):
    """Function get_available_vehicles SELECTS a vehicle from the database based on availability.
      Availability is two parameters, start date and end date.  get_available_vehicles only returns
      vehicles that are between start and end dates."""
    try:
        start = environ['dispatcher.groups']['start']
        throwaway = time.strptime(start, '%Y-%m-%d %H:%M')
        finish = environ['dispatcher.groups']['finish']
        throwaway = time.strptime(finish, '%Y-%m-%d %H:%M')
        try:
            record_id = environ['dispatcher.groups']['record_id']
        except:
            record_id = -1
        sql_statement = Template(select_available_vehicles).substitute(locals())
        rows = query(environ['connection'], sql_statement)
        output = {}
        labels = ['id', 'vehicleno', 'capacity', 'type', 'tagno', 'doortype', 'weight', 'status'] 
        output['vehicles'] = [dict(zip(labels,i)) for i in rows]
    except:
        start_response(status_codes.error, content_headers.default_plain())
        return ['Error']
    return json_app(output)(environ, start_response)
    
def get_affected_schedule_events(environ, vehicle_id, start, finish):
    """Function get_affected_schedule_events selects affected rows in the database."""
    affected_list = []
    sql = get_affected_events_sql.substitute(locals())
    rows = query(environ['connection'], sql)
    affected_list = [(row.parent_id or row.id) for row in rows]
    return affected_list
